import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class demo_0503 {
    public static void main(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();

        //String sql = "delete from borrow_info  bi where bi.id = (select id from bi order by id desc limit 1)";
        String sql = "delete from borrow_info where id = (select r.id from (select max(id) id from borrow_info) r)";
    }

    public static void main3(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();

        String sql = "update book set price = 61.20 where name ='深入理解Java虚拟机'";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        System.out.println(preparedStatement.executeUpdate());

        preparedStatement.close();
        connection.close();
    }

    public static void main2(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();

        String sql2 = "select s.name, b.name, b.author, bi.start_time, bi.end_time from borrow_info bi join student s on s.id = bi.student_id join book b on b.id = bi.book_id join category c on c.id = b.category_id where c.name = '计算机'";
        PreparedStatement preparedStatement = connection.prepareStatement(sql2);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            System.out.print(resultSet.getString("s.name")+"\t"); // 使用查询中定义的别名
            System.out.print(resultSet.getString("b.name")+"\t");
            System.out.print(resultSet.getString("b.author")+"\t");
            System.out.print(resultSet.getString("bi.start_time")+"\t");
            System.out.print(resultSet.getString("bi.end_time")+"\t");
            System.out.println();
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();

    }


    public static void main1(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();



        String sql1 = "insert into borrow_info values(19,(select id from book where name = '诗经'),(select id from student where name = '貂蝉'),'2019-09-25 17:50:00','2019-10-25 17:50:00')";
        PreparedStatement preparedStatement = connection.prepareStatement(sql1);
        System.out.println(preparedStatement.executeUpdate());

        preparedStatement.close();
        connection.close();
    }
}